import scrapy
from gbifSpider.items import GbifspiderImageItem
from gbifSpider.settings import DB_SETTINGS
from twisted.enterprise import adbapi
import pymysql
import time


class GbifImgSpider(scrapy.Spider):
    name = "gbifImg"
    allowed_domains = []

    def start_requests(self):
        while True:
            self.update_undoing()
            gbif_list = self.mysql_query()
            self.update_doing(gbif_list)
            for gbif in gbif_list:
                item = GbifspiderImageItem()
                item['id'] = gbif['id']
                item['image'] = gbif['image']
                item['imgId'] = gbif['img_id']
                item['imgKey'] = gbif['img_key']
                item['usageKey'] = gbif['usage_key']
                item['scientificName'] = gbif['scientificName']
                yield item
    
    def mysql_query(self):

        # 获取数据库配置参数
        db_name = DB_SETTINGS
        db_params = db_name['gbif']

        """
        MySQL查询
        :return:
        """
        # 打开数据库连接
        db = pymysql.connect(host=db_params['host'],
                            user=db_params['user'],
                            password=db_params['password'],
                            database=db_params['db'])
        # 使用 cursor() 方法创建一个游标对象 cursor
        cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
        # SQL语句
        sql = """
        select id, image, img_id, `img_key`, usage_key, scientificName from gbif where is_download = 0 and is_doing = 0 ORDER BY RAND() limit 100;
        """
        # 使用 execute()  方法执行 SQL 查询
        cursor.execute(sql)
        # 获取第一条结果
        gbif = cursor.fetchall()
        
        # 关闭数据库连接
        db.close()
        # 返回查询结果
        return gbif
    
    def update_undoing(self):

        # 获取数据库配置参数
        db_name = DB_SETTINGS
        db_params = db_name['gbif']

        """
        MySQL查询
        :return:
        """
        # 打开数据库连接
        db = pymysql.connect(host=db_params['host'],
                            user=db_params['user'],
                            password=db_params['password'],
                            database=db_params['db'])
        cursor = db.cursor()

        update_sql = "update gbif set is_doing = 0 where is_doing = 1"
        try:
            cursor.execute(update_sql)
            print("gbif un_doing数据更新成功 => " + update_sql)
        except Exception as e:
            print("gbif un_doing执行sql异常 => " + str(e))
            pass
        finally:
            # 关闭数据库连接
            db.close()

    def update_doing(self, gbif_list):

        if len(gbif_list) == 0:
            return
        
        value_id = []

        for gbif in gbif_list:
            value_id.append(str(gbif['id']))

        ids = ','.join(value_id)

        # 获取数据库配置参数
        db_name = DB_SETTINGS
        db_params = db_name['gbif']

        """
        MySQL查询
        :return:
        """
        # 打开数据库连接
        db = pymysql.connect(host=db_params['host'],
                            user=db_params['user'],
                            password=db_params['password'],
                            database=db_params['db'])
        cursor = db.cursor()

        update_sql = "update gbif set is_doing = 1 where id in (%s) " % (ids)
        try:
            cursor.execute(update_sql)
            print("gbif doing数据更新成功 => " + update_sql)
        except Exception as e:
            print("gbif doing执行sql异常 => " + str(e))
            pass
        finally:
            # 关闭数据库连接
            db.close()